import logging
from typing import Optional
from fastapi import APIRouter, Depends, Query, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

tc_overtime_router = APIRouter(prefix="/tcOvertime", tags=["加班管理"], dependencies=[Depends(oauth2_scheme)])


class LayerSearch(PaginationBase):
    fsiteNo: str = Field("", description="站点编号")
    userId: str = Field("", description="用户id")
    fbegdate: str = Field("", description="开始时间")
    fenddate: str = Field("", description="结束时间")


@tc_overtime_router.get("/list", summary="加班查询接口")
def list_layer(layer: LayerSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_tc_overtime where 1=1"
    if layer.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = layer.fsiteNo
    if layer.userId != "":
        sql = sql + " and user_id = :userId "
        param["userId"] = layer.userId
    if (layer.fbegdate != "") and (layer.fenddate != ""):
        sql = sql + " and ((to_date(to_char(fbegdate,'yyyymmdd'),'yyyymmdd')  between :fbegdate and :fenddate )"
        param["fbegdate"] = layer.fbegdate
        param["fenddate"] = layer.fenddate
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fid,fsite_no,user_id,fbegdate,fenddate,"
    sql = sql + " fbegtime,fendtime,fnote,fcheckdate,fcheckuser,"
    sql = sql + " fbillstate,fpoint_no,fupddate"
    sql = sql + " from a_tc_overtime where 1=1"
    if layer.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = layer.fsiteNo
    if layer.userId != "":
        sql = sql + " and user_id = :userId "
        param["userId"] = layer.userId
    if (layer.fbegdate != "") and (layer.fenddate != ""):
        sql = sql + " and ((to_date(to_char(fbegdate,'yyyymmdd'),'yyyymmdd')  between :fbegdate and :fenddate )"
        sql = sql + " and (to_date(to_char(fenddate,'yyyymmdd'),'yyyymmdd')  between :fbegdate and :fenddate ))"
        param["fbegdate"] = layer.fbegdate
        param["fenddate"] = layer.fenddate
    if layer.order == "desc":
        sql = sql + " order by  fid desc "
    else:
        sql = sql + " order by  fid asc "
    sql = sql + "limit :limit offset :offset "
    param["offset"] = layer.offset
    param["limit"] = layer.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['userId'] = data[key][2]
        d1['fbegdate'] = data[key][3]
        d1['fenddate'] = data[key][4]
        d1['fbegtime'] = data[key][5]
        d1['fendtime'] = data[key][6]
        d1['fnote'] = data[key][7]
        d1['fcheckdate'] = data[key][8]
        d1['fcheckuser'] = data[key][9]
        d1['fbillstate'] = data[key][10]
        d1['fpointNo'] = data[key][11]
        d1['fupddate'] = data[key][12]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": layer.limit, "page": layer.pageIndex,
                 "pages": (count + layer.limit - 1) // layer.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class OvertimeCreate(BaseModel):
    """加班资料新增请求参数"""
    fbegdate: str = Field(..., description="加班开始日期(yyyy-MM-dd)")
    fbegtime: str = Field(..., description="加班开始时间(HH:mm)")
    fbillstate: int = Field(..., description="单据状态编码")
    fenddate: str = Field(..., description="加班结束日期(yyyy-MM-dd)")
    fendtime: str = Field(..., description="加班结束时间(HH:mm)")
    fsiteNo: str = Field(..., description="站点编号")
    userId: int = Field(..., description="操作人ID")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fnote: Optional[str] = Field("", description="备注信息")
    fid: str = Field(None, description="id,修改时填入")


@tc_overtime_router.post("/create", summary="加班资料新增接口")
def create_layer(create: OvertimeCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fid from a_tc_overtime "
            sql = sql + " where 1=1"
            sql = sql + " and ((fbegdate between :fbegdate and :fenddate )"
            sql = sql + " or(fenddate between :fbegdate and :fenddate ))"
            data = db.execute(text(sql), {
                "fbegdate": create.fbegdate,
                "fenddate": create.fenddate,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="该日期已设定")
            else:
                # 先取单号
                fid = ""
                sql = "select nextval('a_tc_overtime_seq') as last_value"
                res = db.execute(text(sql)).fetchall()
                fid = res[0][0]

                sql = "insert into a_tc_overtime(fid,fsite_no,user_id,fbegdate,fenddate,fbegtime,fendtime,fnote,fpoint_no,fbillstate,fupddate)"
                sql = sql + " values( :fid , :fsiteNo , :userId , :fbegdate , :fenddate , :fbegtime , :fendtime , :fnote , :fpointNo , :fbillstate ,now());"
                db.execute(text(sql), {
                    "fid": fid,
                    "fsiteNo": create.fsiteNo,
                    "userId": user_id,
                    "fbegdate": create.fbegdate,
                    "fenddate": create.fenddate,
                    "fbegtime": create.fbegtime,
                    "fendtime": create.fendtime,
                    "fnote": create.fnote,
                    "fpointNo": create.fpointNo,
                    "fbillstate": create.fbillstate,
                })
                # 返回
                d1 = {}
                d1['fid'] = fid
                d1['fsiteNo'] = create.fsiteNo
                d1['fpointNo'] = create.fpointNo
                d1['userId'] = user_id
                d1['fbegdate'] = create.fbegdate
                d1['fenddate'] = create.fenddate
                d1['fbegtime'] = create.fbegtime
                d1['fendtime'] = create.fendtime
                d1['fnote'] = create.fnote
                d1['fcheckdate'] = None
                d1['fcheckuser'] = None
                d1['fbillstate'] = create.fbillstate
                d1['fupddate'] = None
                d1['fpointName'] = None
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@tc_overtime_router.post("/update", summary="")
def update_layer(update: OvertimeCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_tc_overtime"
            sql = sql + " set user_id= :userId ,fbillstate= :fbillstate "
            sql = sql + ",fbegdate= :fbegdate ,fbegtime= :fbegtime "
            sql = sql + ",fenddate= :fenddate ,fendtime= :fendtime "
            sql = sql + ",fpoint_no= :fpointNo "
            sql = sql + ",fnote= :fnote ,fupddate=now()"
            sql = sql + " where fid= :fid "
            db.execute(text(sql), {
                "userId": user_id,
                "fbillstate": update.fbillstate,
                "fbegdate": update.fbegdate,
                "fbegtime": update.fbegtime,
                "fenddate": update.fenddate,
                "fendtime": update.fendtime,
                "fpointNo": update.fpointNo,
                "fnote": update.fnote,
                "fid": update.fid,
            })
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@tc_overtime_router.post("/delete/{id}", summary="删除加班")
def delete_layer(id: str = Path(description="id编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from a_tc_overtime where fid= :id "
            db.execute(text(sql), {"id": id})
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
